import pymysql
import pandas as pd

# 连接到MySQL数据库
db = pymysql.connect(host='localhost', port=23306, user='root', password='Pr0dkcd_', db="kcdoc")
cursor = db.cursor()
data = pd.read_excel('/Users/xuyihang/Desktop/error_plan.xlsx', sheet_name='Sheet3', usecols=['plan'])
for index, row in data.iterrows():
    plan_id = row['plan']
    # 第一个查询
    first_query = """
        SELECT plan_id, raw_material_num, count(raw_material_num)
        FROM kc_raw_quota
        WHERE quantity = remain_quantity
        AND plan_id = {0}
        GROUP BY plan_id, raw_material_num, plan_detail_id
        HAVING count(raw_material_num) > 1
    """.format(plan_id)
    cursor.execute(first_query)

    # 获取第一个查询结果
    first_results = cursor.fetchall()

    # 获取第一个查询结果中的raw_material_num
    raw_material_nums = [row[1] for row in first_results]
    if not raw_material_nums:
        continue
    raw_material_nums_str = ', '.join("'{}'".format(num) for num in raw_material_nums)

    # 第二个查询
    second_query = """
        SELECT min(id), raw_material_num
        FROM kc_raw_quota
        WHERE raw_material_num IN ({0})
        AND plan_id = {1}
        GROUP BY raw_material_num
    """.format(raw_material_nums_str, plan_id)

    cursor.execute(second_query)

    # 获取第二个查询结果
    second_results = cursor.fetchall()

    # 获取第二个查询结果中的id
    ids = [row[0] for row in second_results]

    # 第三个查询
    third_query = """
        SELECT *
        FROM kc_raw_quota
        WHERE id NOT IN %(ids)s
        AND raw_material_num IN %(raw_material_nums)s
        AND plan_id = %(plan_id)s
    """
    params = {
        'ids': ids,
        'raw_material_nums': raw_material_nums,
        'plan_id': plan_id
    }
    cursor.execute(third_query, params)

    # 获取第三个查询结果
    third_results = cursor.fetchall()
    # print("id",plan_id)
    # 打印第三个查询结果
    for row in third_results:
        print(row[0])




# 关闭游标和数据库连接
cursor.close()
db.close()
